--Create the People Table
DECLARE @People TABLE 
    (
    ID INT PRIMARY KEY IDENTITY(1,1),
    Name VARCHAR(25), 
    [Address] VARCHAR(25), 
    Phone CHAR(10), 
    Gender CHAR(1),
    UpdateID VARCHAR(25)
    )

--Insert the existing people
INSERT INTO @People (Name, [Address],Phone, Gender,UpdateID) VALUES
    ('Will','123 North Pole','5555555555','M','MATT'),
    ('Kelly','456 Equator','5555555555','M','MATT'),
    ('Sunita','789 South Pole','5555555555','M','MATT')
SELECT * FROM @People

--Create the New People Table
DECLARE @NewPeople TABLE 
    (
	   Name VARCHAR(25), 
	   [Address] VARCHAR(25), 
	   Phone CHAR(10), 
	   Gender CHAR(1)
    )
    
--Insert the people for UPSERT
INSERT INTO @NewPeople (Name, [Address],Phone, Gender) VALUES
    ('Will','123 Greenland','2085555555','M'),
    ('Amy','1 Island','5555555555','F'),
    ('Sunita','789 South Pole','5555555555','F')
SELECT * FROM @NewPeople  






/*    
UPDATE WILL (He moved)
ADD Amy (She's a new member)
UPDATE SUNITA (She's not a He)
LEAVE KELLY ALONE
*/


--***METHOD 1 - WITHOUT MERGE
--UPDATE  P SET 
--	   P.[Address] = NP.[Address],
--	   P.Phone = NP.Phone,
--	   P.Gender = NP.Gender
--FROM	   @People AS P
--	   INNER JOIN @NewPeople AS NP ON (P.Name = NP.Name)


--INSERT  INTO @People (Name, [Address], Phone, Gender,UpdateID)
--SELECT  NP.Name, NP.[Address], NP.Phone, NP.Gender, 'MATT' AS UpdateID
--FROM	   @NewPeople AS NP
--	   LEFT JOIN @People AS P ON (P.Name = NP.Name)
--WHERE   P.ID IS NULL

--SELECT * FROM @People













/*    
UPDATE WILL (He moved)
ADD Amy (She's a new member)
UPDATE SUNITA (She's not a He)
LEAVE KELLY ALONE
*/

----***METHOD 2 - USING MERGE
MERGE   INTO @People AS T
USING   @NewPeople AS S
ON	   (T.Name = S.Name)
WHEN	   MATCHED THEN --AND T.UpdateID <> 'MATT'
	   UPDATE SET T.[Address] = S.[Address], T.Phone = S.Phone, T.Gender = S.Gender, T.UpdateID = 'MATT'
WHEN	   NOT MATCHED THEN
	   INSERT (Name,[Address],Phone,Gender,UpdateID) VALUES (S.Name,S.[Address],S.Phone,S.Gender,'MATT')
OUTPUT  $action, inserted.Name ;
 
SELECT * FROM @People

    
